#!/bin/bash
APP=edu

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else
    do_date=`date -d "-1 day" +%F`
fi

dws_test_exam_nd="
insert overwrite table dws_test_exam_nd partition(dt='${do_date}')
select paper_id,
       paper_title,
       course_id,
       course_name,

       count(if (dt >= date_sub('${do_date}',6),1,null)) num_7d,
       count(distinct if (dt >= date_sub('${do_date}',6),user_id,null)) user_num,
       sum(if (dt >= date_sub('${do_date}',6),score,0)) score_sum,
       sum(if (dt >= date_sub('${do_date}',6),duration_sec,0)) duration_sec_sum,

       count(if (dt >= date_sub('${do_date}',29),1,null)) num_30d,
       count(distinct if (dt >= date_sub('${do_date}',29),user_id,null)) user_num_30d,
       sum(if (dt >= date_sub('${do_date}',29),score,0)) score_sum_30d,
       sum(if (dt >= date_sub('${do_date}',29),duration_sec,0)) duration_sec_sum_30d

from dwd_test_exam_inc
where dt>=date_sub('${do_date}',29)
group by paper_id, paper_title,course_id,course_name;
"
dws_test_course_scorestage_nd="
insert overwrite table dws_test_course_scorestage_nd partition(dt='${do_date}')
select course_id,
       course_name,
       score_stage,

       count(distinct if (dt >= date_sub('${do_date}',6),user_id,null)) user_num_7d,
       count(if (dt >= date_sub('${do_date}',6),1,null)) num_7d,

       count(distinct if (dt >= date_sub('${do_date}',29),user_id,null)) user_num_30d,
       count(if (dt >= date_sub('${do_date}',29),1,null)) num_30d

from dwd_test_exam_inc
where dt>=date_sub('${do_date}',29)
group by course_id, course_name,score_stage;
"
dws_test_question_nd="
insert overwrite table dws_test_question_nd partition(dt='${do_date}')
select question_id,
       question_txt,

       sum(if(is_correct = '1' and dt>=date_sub('${do_date}',6),1,0)) correct_num_7d,
       count(if (dt >= date_sub('${do_date}',6),1,null)) total_num_7d,

       sum(if(is_correct = '1'and dt>=date_sub('${do_date}',29),1,0)) correct_num_30d,
       count(if (dt >= date_sub('${do_date}',29),1,null)) total_num_30d

from dwd_test_exam_inc
where dt>=date_sub('${do_date}',29)
group by question_id, question_txt;
"
dws_trade_user_province_order_nd="
insert overwrite table dws_trade_user_province_order_nd partition (dt = '${do_date}')
select user_id,
       province_id,
       province_name,
       area_code,
       iso_code,
       iso_3166_2,
       sum(if(dt>date_sub('${do_date}',7),order_count_1d,0)) order_count_7d,
       sum(if(dt>date_sub('${do_date}',7),order_num_1d,0)) order_num_7d,
       sum(if(dt>date_sub('${do_date}',7),order_original_amount_1d,0)) order_original_amount_7d,
       sum(if(dt>date_sub('${do_date}',7),coupon_reduce_amount_1d,0)) coupon_reduce_amount_7d,
       sum(if(dt>date_sub('${do_date}',7),order_total_amount_1d,0)) order_total_amount_7d,
       sum(order_count_1d),
       sum(order_num_1d),
       sum(order_original_amount_1d),
       sum(coupon_reduce_amount_1d),
       sum(order_total_amount_1d)
from dws_trade_user_province_order_1d
where dt <='${do_date}' and dt > date_sub('${do_date}',30)
group by user_id,province_id, province_name, area_code, iso_code, iso_3166_2;
"

case $1 in
    "dws_test_exam_nd" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_test_exam_nd"
    ;;
    "dws_test_course_scorestage_nd" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_test_course_scorestage_nd"
    ;;
    "dws_test_question_nd" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_test_question_nd"
    ;;
    "dws_trade_user_province_order_nd" )
        /opt/module/hive/bin/hive -e "use ${APP};$dws_trade_user_province_order_nd"
    ;;



    "all" )
        /opt/module/hive/bin/hive -e "
        use ${APP};
        $dws_test_exam_nd
        $dws_test_course_scorestage_nd
        $dws_test_question_nd
        $dws_trade_user_province_order_nd

        "
    ;;
esac